package avicit.bdp.dgs.qa.utils.coverter;

import avicit.bdp.dgs.qa.dto.*;
import avicit.bdp.dgs.qa.service.RuleTemplateService;
import avicit.bdp.dgs.qa.utils.enums.MeasureType;
import avicit.bdp.dgs.qa.utils.enums.TemplateType;
import avicit.platform6.core.exception.BusinessException;
import com.alibaba.fastjson2.JSON;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @金航数码科技有限责任公司
 * @作者：developer
 * @邮箱：developer@avic-digital.com
 * @创建时间： 2023-04-17
 * @类说明：SqlTemplateConverter
 * @修改记录：
 * @注意事项：
 * @主要功能：SQL转换 1、支持单表校验SQL转换（异常SQL、表行数统计SQL）
 * 2、支持跨表校验SQL转换（异常SQL、表行数统计SQL）
 * 3、支持跨库校验SQL转换（异常SQL、表行数统计SQL）
 */
@Component
public class SqlTemplateConverter {
    private static final Logger logger = LoggerFactory.getLogger(SqlTemplateConverter.class);

    /**
     * SQL中自定义参数
     */
    private static final String DB_NAME_PLACEHOLDER = "\\$\\{db\\}";
    private static final String TABLE_NAME_PLACEHOLDER = "\\$\\{table\\}";
    private static final String FIELD_NAME_PLACEHOLDER = "\\$\\{field\\}";
    private static final String FILTER_NAME_PLACEHOLDER = "\\$\\{filter\\}";
    private static final String SRC_TABLE_PLACEHOLDER = "\\$\\{src_table\\}";
    private static final String SRC_FILTER_PLACEHOLDER = "\\$\\{src_filter\\}";
    private static final String TARGET_TABLE_PLACEHOLDER = "\\$\\{target_table\\}";
    private static final String TARGET_FILTER_PLACEHOLDER = "\\$\\{target_filter\\}";

    @Autowired
    private RuleTemplateService ruleTemplateService;

    /**
     * 对外接口--支持单表校验SQL转换（异常SQL、表行数统计SQL）
     *
     * @param ruleDTO
     */
    public void covertSingleSrcSQL(RuleDTO ruleDTO) {
        if (ruleDTO == null) {
            throw new BusinessException("规则为空");
        }

        // 异常SQL转换
        TemplateType templateType = TemplateType.getTypeByCode(ruleDTO.getTemplateType());
        switch (templateType) {
            case BUILT_IN:
            case CUSTOM:
                singleSrcAbnormalSqlConvert(ruleDTO);
                break;
            case CUSTOM_SQL:
                break;
            default:
                throw new BusinessException("暂不支持" + templateType.getDesc());
        }

        // 表行数SQL转换
        singelSrcNormalSqlConvert(ruleDTO);
    }

    /**
     * 对外接口--支持跨表校验SQL转换（异常SQL、表行数统计SQL）
     *
     * @param ruleDTO
     */
    public void covertMultiSrcSQL(RuleDTO ruleDTO) {
        if (ruleDTO == null) {
            throw new BusinessException("规则为空");
        }

        // 异常SQL转换
        TemplateType templateType = TemplateType.getTypeByCode(ruleDTO.getTemplateType());
        switch (templateType) {
            case BUILT_IN:
            case CUSTOM:
                multiSrcAbnormalSqlConvert(ruleDTO);
                break;
            case CUSTOM_SQL:
                break;
            default:
                throw new BusinessException("暂不支持" + templateType.getDesc());
        }
    }

    /**
     * 对外接口--支持跨库校验SQL转换（异常SQL、表行数统计SQL）
     *
     * @param ruleDTO
     */
    public void covertMultiDbSQL(RuleDTO ruleDTO) {
        throw new BusinessException("暂不支持该功能");
    }

    /**
     * 规则模板总sql替换为真实db、table、field、filter
     * 注意：
     * 1、当前仅支持单表校验
     *
     * @param ruleDTO
     * @return
     */
    public void singleSrcAbnormalSqlConvert(RuleDTO ruleDTO) {
        if (ruleDTO == null) {
            throw new BusinessException("质量规则为null，生成sql失败.");
        }
        if (CollectionUtils.isEmpty(ruleDTO.getDataSourceList())) {
            throw new BusinessException("质量规则未配置数据源信息，生成sql失败.");
        }
        if (StringUtils.isBlank(ruleDTO.getTemplateId())) {
            throw new BusinessException("质量规则未配置规则模板，生成sql失败.");
        }
        if (MeasureType.SINGLE_SOURCE.getCode() != ruleDTO.getMeasureType()) {
            throw new BusinessException("当前仅支持单表校验，生成sql失败.");
        }

        // 替换数据源信息，包括db、table、field、filter
        String sql = replaceSingleDsInfo(ruleDTO);

        // 替换模板参数，例如正则表达式、日期格式、数据范围等
        ruleDTO.setAbnormalSql(replaceArguement(ruleDTO, sql));
    }

    /**
     * 规则模板总sql替换为真实db、table、field、filter
     * 注意：
     * 1、当前仅支持单表校验
     *
     * @param ruleDTO
     * @return
     */
    public void multiSrcAbnormalSqlConvert(RuleDTO ruleDTO) {
        if (ruleDTO == null) {
            throw new BusinessException("质量规则为null，生成sql失败.");
        }
        if (CollectionUtils.isEmpty(ruleDTO.getDataSourceList())) {
            throw new BusinessException("质量规则未配置数据源信息，生成sql失败.");
        }
        if (StringUtils.isBlank(ruleDTO.getTemplateId())) {
            throw new BusinessException("质量规则未配置规则模板，生成sql失败.");
        }
        if (MeasureType.MULTI_SOURCE.getCode() != ruleDTO.getMeasureType()) {
            throw new BusinessException("当前仅支持跨表校验，生成sql失败.");
        }

        // 替换数据源信息，包括db、table、field、filter
        ruleDTO.setAbnormalSql(replaceMultiDsInfo(ruleDTO));
    }

    /**
     * 规则模板总sql替换为真实db、table、field、filter
     * 注意：
     * 1、当前仅支持单表校验
     *
     * @param ruleDTO
     * @return
     */
    public void singelSrcNormalSqlConvert(RuleDTO ruleDTO) {
        if (ruleDTO == null) {
            throw new BusinessException("质量规则为null，生成sql失败.");
        }
        if (CollectionUtils.isEmpty(ruleDTO.getDataSourceList())) {
            throw new BusinessException("质量规则未配置数据源信息，生成sql失败.");
        }
        if (ruleDTO.getTemplateType() == null) {
            throw new BusinessException("模板类型为配置，生成sql失败.");
        }
        if (ruleDTO.getTemplateType() != TemplateType.CUSTOM_SQL.getCode()) {
            if (StringUtils.isBlank(ruleDTO.getTemplateId())) {
                throw new BusinessException("质量规则未配置规则模板，生成sql失败.");
            }
        }
        if (MeasureType.SINGLE_SOURCE.getCode() != ruleDTO.getMeasureType()) {
            throw new BusinessException("当前仅支持单表校验，生成sql失败.");
        }

        StringBuffer sb = new StringBuffer();
        QaDataSourceDTO currDs = ruleDTO.getDataSourceList().get(0);
        sb.append("select count(1) as value from ");
        if (StringUtils.isNotEmpty(currDs.getTableName())) {
            sb.append(currDs.getTableName());
        }
        if (StringUtils.isNotEmpty(currDs.getFilter())) {
            sb.append(" where ").append(currDs.getFilter());
        }

        ruleDTO.setNormalSql(sb.toString());
    }

    /**
     * 替换数据源信息，包括db、table、field、filter
     *
     * @param ruleDTO
     * @return
     */
    private String replaceSingleDsInfo(RuleDTO ruleDTO) {
        RuleTemplateDTO ruleTemplate = this.ruleTemplateService.getRuleTemplateDetail(ruleDTO.getTemplateId());
        if (ruleTemplate == null) {
            throw new BusinessException("未找到规则模板，生成sql失败，templateId=." + ruleDTO.getTemplateId());
        }
        if (StringUtils.isBlank(ruleTemplate.getCustomSQL())) {
            throw new BusinessException("规则模板中未配置SQL，生成sql失败.");
        }

        QaDataSourceDTO currDs = ruleDTO.getDataSourceList().get(0);
        String sql = ruleTemplate.getCustomSQL();
        if (StringUtils.isNotEmpty(currDs.getDbName())) {
            sql = sql.replaceAll(DB_NAME_PLACEHOLDER, currDs.getDbName());
        }
        if (StringUtils.isNotEmpty(currDs.getTableName())) {
            sql = sql.replaceAll(TABLE_NAME_PLACEHOLDER, currDs.getTableName());
        }
        if (StringUtils.isNotEmpty(currDs.getField())) {
            sql = sql.replaceAll(FIELD_NAME_PLACEHOLDER, currDs.getField());
        }
        if (StringUtils.isNotEmpty(currDs.getFilter())) {
            sql = sql.replaceAll(FILTER_NAME_PLACEHOLDER, currDs.getFilter());
        } else {
            // 注意：不能替换为true，因为Oracle等数据库不支持true
            sql = sql.replaceAll(FILTER_NAME_PLACEHOLDER, "1=1");
        }

        return sql;
    }

    /**
     * 替换数据源信息，包括src_table、src_filter、target_table、target_filter
     *
     * @param ruleDTO
     * @return
     */
    private String replaceMultiDsInfo(RuleDTO ruleDTO) {
        RuleTemplateDTO ruleTemplate = this.ruleTemplateService.getRuleTemplateDetail(ruleDTO.getTemplateId());
        if (ruleTemplate == null) {
            throw new BusinessException("未找到规则模板，生成sql失败，templateId=." + ruleDTO.getTemplateId());
        }
        if (StringUtils.isBlank(ruleTemplate.getCustomSQL())) {
            throw new BusinessException("规则模板中未配置SQL，生成sql失败.");
        }

        int inx = 1;
        String sql = ruleTemplate.getCustomSQL();
        for (QaDataSourceDTO currDs : ruleDTO.getDataSourceList()) {
            if (inx == 1) {
                // 左表
                if (StringUtils.isNotEmpty(currDs.getTableName())) {
                    sql = sql.replaceAll(SRC_TABLE_PLACEHOLDER, currDs.getTableName());
                }

                String filter = currDs.getFilter();
                if (StringUtils.isNotEmpty(currDs.getFilter())) {
                    sql = sql.replaceAll(SRC_FILTER_PLACEHOLDER, currDs.getFilter());
                } else {
                    // 注意：不能替换为true，因为Oracle等数据库不支持true
                    sql = sql.replaceAll(SRC_FILTER_PLACEHOLDER, "1=1");
                }
            } else {
                // 右表
                if (StringUtils.isNotEmpty(currDs.getTableName())) {
                    sql = sql.replaceAll(TARGET_TABLE_PLACEHOLDER, currDs.getTableName());
                }

                String filter = currDs.getFilter();
                if (StringUtils.isNotEmpty(currDs.getFilter())) {
                    sql = sql.replaceAll(TARGET_FILTER_PLACEHOLDER, currDs.getFilter());
                } else {
                    // 注意：不能替换为true，因为Oracle等数据库不支持true
                    sql = sql.replaceAll(TARGET_FILTER_PLACEHOLDER, "1=1");
                }
            }

            inx++;
        }

        return sql;
    }

    /**
     * 替换规则模板参数
     *
     * @param ruleDTO
     * @param sql
     * @return
     */
    private String replaceArguement(RuleDTO ruleDTO, String sql) {
        if (StringUtils.isEmpty(sql)) {
            return sql;
        }
        if (ruleDTO == null) {
            return sql;
        }
        if (StringUtils.isEmpty(ruleDTO.getVariables())) {
            return sql;
        }

        List<RuleTemplateArguementDTO> arguementList = JSON.parseArray(ruleDTO.getVariables(), RuleTemplateArguementDTO.class);
        if (CollectionUtils.isEmpty(arguementList)) {
            return sql;
        }
        for (RuleTemplateArguementDTO arg : arguementList) {
            if (arg == null) {
                continue;
            }
            if (StringUtils.isEmpty(arg.getPlaceholder())) {
                continue;
            }
            if (StringUtils.isEmpty(arg.getArgVaule())) {
                continue;
            }

            String placeHolder = "\\$\\{" + arg.getPlaceholder() + "}";
            sql = sql.replaceAll(placeHolder, arg.getArgVaule());
        }

        return sql;
    }
}
